﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Threading;
using System.Windows.Forms;
using DevelopTool.Bll.DbBlls;
using DevelopTool.Bll.TipsBlls.Jobs;
using DevelopTool.Common;
using DevelopTool.Forms.BaseForms;
using DevelopTool.Model;
using DevelopTool.Model.DbModels;
using DevelopTool.Model.Enums;
using DevelopTool.Model.TipsModels;
using Sunny.UI;

namespace DevelopTool.Forms.DbForms.DbLockWatchForms
{
    /// <summary>
    /// 锁表监控
    /// </summary>
    public partial class DbLockWatchForm : BaseUIPage
    {
        /// <summary>
        /// 锁表通知时间 3分钟
        /// </summary>
        private int lockTime = 180;

        /// <summary>
        /// 连接信息
        /// </summary>
        private DbInfo dbInfo;

        private DbManagerBll dbManagerBll;

        /// <summary>
        /// 结果
        /// </summary>
        private DataTable resultTable;

        /// <summary>
        /// 锁定的会话已提醒的
        /// </summary>
        private HashSet<string> lockSessionTipsStr = new HashSet<string>();

        /// <summary>
        /// 提醒窗口
        /// </summary>
        private TipsShowForm tipsShowForm;

        /// <summary>
        /// DataGridView列信息集合
        /// </summary>
        private List<DataGridColumn> dataGridColumns = new List<DataGridColumn>()
              {
                { new DataGridColumn("服务器"){ColumnText="服务器",Width=-1}},
                { new DataGridColumn("实例"){ColumnText="实例",Width=100}},
                { new DataGridColumn("用户"){ColumnText="用  户",Width=-1}},
                { new DataGridColumn("锁类型"){ColumnText="锁类型",Width=-1}},
                { new DataGridColumn("锁对象"){ColumnText="锁对象",Width=0}},
                { new DataGridColumn("串口号"){ColumnText="串口号",Width=-1}},
                { new DataGridColumn("会话ID"){ColumnText="会话ID",Width=-1,ColumnType=ColumnType.RowMain}},
                { new DataGridColumn("是否被锁"){ColumnText="是否被锁",Width=0,ColumnType=ColumnType.Important}},
                { new DataGridColumn("锁时长"){ColumnText="锁时长",Width=70}},
                { new DataGridColumn("SQL语句"){ColumnText="SQL语句",Width=150,ColumnType=ColumnType.LongText,ShowFormProperty=new ShowFormProperty(){
                BaseNameColumn="会话ID"
                }}},
                { new DataGridColumn("客户端IP"){ColumnText="客户端IP",Width=-1}},
                { new DataGridColumn("客户端名称"){ColumnText="客户端名称",Width=0}},
                { new DataGridColumn("程序名称"){ColumnText="程序名称",Width=0}},
                { new DataGridColumn("系统用户名"){ColumnText="系统用户名",Width=-1}},
                { new DataGridColumn("窗口标题"){ColumnText="窗口标题",Width=-1,ColumnType=ColumnType.LongText,ShowFormProperty=new ShowFormProperty(){
                BaseNameColumn="会话ID"
                }}},

                { new DataGridColumn("锁定会话ID"){ColumnText="锁定会话ID",Width=20,ColumnType=ColumnType.Hide}},
                { new DataGridColumn("等待时间"){ColumnText="等待时间",Width=20,ColumnType=ColumnType.Hide}},
                { new DataGridColumn("内存地址"){ColumnText="内存地址",Width=20,ColumnType=ColumnType.Hide}},
              };

        public DbLockWatchForm()
        {
            InitializeComponent();
        }


        private void DbLockWatchForm_Load(object sender,EventArgs e)
        {
            this.Disposed += this.DbLockWatchForm_Disposed;

            resultTable = new DataTable("ResultTable");
            resultTable.Columns.Add("服务器",typeof(string));
            resultTable.Columns.Add("实例",typeof(string));
            resultTable.Columns.Add("用户",typeof(string));
            resultTable.Columns.Add("锁类型",typeof(string));
            resultTable.Columns.Add("锁对象",typeof(string));
            resultTable.Columns.Add("串口号",typeof(decimal));
            resultTable.Columns.Add("会话ID",typeof(decimal));
            resultTable.Columns.Add("是否被锁",typeof(string));
            resultTable.Columns.Add("锁时长",typeof(string));
            resultTable.Columns.Add("SQL语句",typeof(string));
            resultTable.Columns.Add("系统用户名",typeof(string));
            resultTable.Columns.Add("客户端IP",typeof(string));
            resultTable.Columns.Add("客户端名称",typeof(string));
            resultTable.Columns.Add("程序名称",typeof(string));
            resultTable.Columns.Add("窗口标题",typeof(string));

            resultTable.Columns.Add("锁定会话ID",typeof(decimal));
            resultTable.Columns.Add("等待时间",typeof(decimal));
            resultTable.Columns.Add("内存地址",typeof(string));

            this.dataDisplayControl.BindData(resultTable,this.dataGridColumns);

        }




        /// <summary>
        /// 数据库连接下拉框选择事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dbinfoSelectCombo_DbInfoSelected(object sender,CommonControls.DbinfoEventArgs e)
        {
            if(e.DbInfo != null && e.IsConnect)
            {
                this.dbInfo = e.DbInfo;
                this.dbManagerBll = new DbManagerBll(this.dbInfo);
            }
            else
            {
                this.dbInfo = null;
                this.dbManagerBll = null;
            }
        }


        /// <summary>
        /// 查询按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnQueryLock_Click(object sender,EventArgs e)
        {
            ManualQueryLockData();
        }

        private void DbLockWatchForm_KeyDown(object sender,KeyEventArgs e)
        {
            if(e.KeyCode == Keys.F8)
            {
                ManualQueryLockData();
            }
            else if(e.KeyCode == Keys.F5)
            {
                this.swcAutoLoad.Active = !this.swcAutoLoad.Active;//取反值
            }
        }


        /// <summary>
        /// 手动查询锁表数据
        /// </summary>
        private void ManualQueryLockData()
        {
            if(this.dbInfo == null || this.dbManagerBll == null)
            {
                this.ShowWarningNotifier("请选择数据库连接");
                return;
            }

            if(this.swcAutoLoad.Active)
            {
                this.ShowWarningNotifier("如需手动查询，请先关闭自动刷新开关");
                return;
            }

            if(backgroundWorker.IsBusy == true || this.btnQueryLock.Enabled == false)//判断backgroundWorker是否正在运行异步操作
            {
                this.ShowWarningNotifier("请等待后台任务执行完成");
                return;
            }

            this.btnQueryLock.Enabled = false;//禁用按钮

            this.resultTable.Clear();

            //开始后台任务
            backgroundWorker.RunWorkerAsync(new Tuple<DbManagerBll,bool,int>(new DbManagerBll(this.dbInfo),false,0));

        }



        /// <summary>
        /// 自动刷新开关事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void swcAutoLoad_ActiveChanged(object sender,EventArgs e)
        {
            if(this.swcAutoLoad.Active)
            {
                if(this.dbInfo == null)
                {
                    this.ShowWarningNotifier("请选择数据库连接");
                    this.swcAutoLoad.Active = false;
                    return;
                }

                if(backgroundWorker.IsBusy)//判断backgroundWorker是否正在运行异步操作
                {
                    this.ShowWarningNotifier("请等待后台任务执行完成");
                    this.swcAutoLoad.Active = false;
                    return;
                }

                if(string.IsNullOrWhiteSpace(this.txtReloadInterval.Text))
                {
                    this.ShowWarningNotifier("请输入自动刷新间隔时间");
                    this.swcAutoLoad.Active = false;
                    return;
                }

                if(this.txtReloadInterval.IntValue < 5)
                {
                    this.ShowWarningNotifier("自动刷新间隔时间不能小于5秒");
                    this.swcAutoLoad.Active = false;
                    return;
                }

                this.btnQueryLock.Enabled = false;

                //开始后台任务
                backgroundWorker.RunWorkerAsync(new Tuple<DbManagerBll,bool,int>(new DbManagerBll(this.dbInfo),true,this.txtReloadInterval.IntValue));
            }
            else
            {
                backgroundWorker.CancelAsync();//取消后台任务
            }
        }



        /// <summary>
        /// 后台任务开始执行
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void backgroundWorker_DoWork(object sender,DoWorkEventArgs e)
        {
            var tuple = e.Argument as Tuple<DbManagerBll,bool,int>;
            if(tuple.Item2 == true)//自动刷新
            {
                int i = 0;
                while(!backgroundWorker.CancellationPending)//每次判断是否请求了取消后台操作,如果为false则退出
                {
                    i++;
                    this.Invoke(new Action(() =>
                    {
                        this.lblRunCount.Text = $"开始第{i}次查询锁表记录...";
                    }));

                    using(DataTable dataTable = tuple.Item1.GetDbLockData())
                    {
                        backgroundWorker.ReportProgress(i,new Tuple<bool,DataTable>(true,dataTable));//报告完成进度
                        Thread.Sleep(tuple.Item3 * 1000);
                    }

                }
            }
            else//手动查询
            {
                using(DataTable dataTable = tuple.Item1.GetDbLockData())
                {
                    backgroundWorker.ReportProgress(1,new Tuple<bool,DataTable>(false,dataTable));//报告完成进度
                }
            }
        }


        /// <summary>
        /// 后台任务执行报告
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void backgroundWorker_ProgressChanged(object sender,ProgressChangedEventArgs e)
        {
            this.resultTable.Clear();

            if(e.UserState is Tuple<bool,DataTable> resultTuple)
            {
                this.resultTable.Merge(resultTuple.Item2);

                this.lblRunCount.Text = "查询次数:" + e.ProgressPercentage;

                //只有自动刷新时才提醒
                if(resultTuple.Item1)
                {
                    List<DataRow> dataRows = resultTuple.Item2.AsEnumerable().Where(row => !string.IsNullOrEmpty(row.Field<string>("是否被锁"))).ToList();

                    if(dataRows != null && dataRows.Count > 0)
                    {
                        foreach(DataRow row in dataRows)
                        {
                            string 会话ID = row["会话ID"].ToString();
                            string 串口号 = row["串口号"].ToString();

                            //锁表时间大于3分钟并且没有提醒过的才继续
                            if(row["等待时间"].ToInt() >= lockTime && lockSessionTipsStr.Add(会话ID + 串口号 + row["服务器"] + row["实例"] + row["内存地址"].ToString() + row["锁对象"].ToString() + row["系统用户名"].ToString() + row["客户端IP"] + row["客户端名称"].ToString() + row["程序名称"].ToString()))
                            {
                                //推送消息
                                if(this.chbSendLockMsg.Checked)
                                {
                                    string dbip = !string.IsNullOrEmpty(this.dbInfo.Ip) ? this.dbInfo.Ip : new DbInfoBll().GetDbInfoByConnectionString(this.dbInfo.ConnectionString).Ip;

                                    DataRow r = resultTuple.Item2.AsEnumerable().FirstOrDefault(it => it.Field<decimal>("会话ID").ToInt() == row["锁定会话ID"].ToInt());//找到对应的锁定行
                                    string killSql = $"alter system kill session '{r["会话ID"]},{r["串口号"]}'";//解锁语句

                                    string msg = $"数据库：{this.dbInfo.NodeName}\\n数据库地址：{dbip}\\n服务器：{row["服务器"]}\\n实例：{row["实例"]}\\n锁类型：{row["锁类型"]}\\n锁对象：{row["锁对象"]}\\n串口号：{串口号}\\n会话ID：{会话ID}\\n疑似锁定会话：{row["是否被锁"]}\\n锁时长：{row["锁时长"]}\\n客户端IP：{row["客户端IP"]}\\n客户端名称：{row["客户端名称"].ToString().Replace(@"\",@"\\")}\\n程序名称：{row["程序名称"].ToString().Replace(@"\",@"\\")}\\n解锁语句：{killSql}";

                                    FeiShuMsgHelper.SendCardMsgAsync(msg,$"数据库会话[{会话ID}]疑似锁表",LogType.Warning);

                                }

                                //弹窗提醒
                                if(this.chbShowTipsForm.Checked)
                                {
                                    this.tipsShowForm?.Close();

                                    this.tipsShowForm = new TipsShowForm(new Tips
                                    {
                                        PlayAudio = true,
                                        NodeName = $"数据库会话[{会话ID}]疑似锁表",
                                        Text = $"锁表会话ID：{会话ID}"
                                    });

                                    this.tipsShowForm.ShowDialog();
                                }

                            }
                        }
                    }
                }

            }
        }


        /// <summary>
        /// 后台任务执行完成
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void backgroundWorker_RunWorkerCompleted(object sender,RunWorkerCompletedEventArgs e)
        {
            this.btnQueryLock.Enabled = true;
        }


        /// <summary>
        /// 结束进程
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExitTask_Click(object sender,EventArgs e)
        {
            if(backgroundWorker.IsBusy)//判断backgroundWorker是否正在运行异步操作
            {
                this.ShowWarningNotifier("请等待后台任务执行完成或停止自动刷新后再结束进程");
                return;
            }

            if(this.dataDisplayControl.DataGridView.SelectedRows.Count > 0)
            {
                if(!this.ShowAskDialog($"{GlobalData.AppName_zh}-敏感操作",$"是否确定结束选定的【{this.dataDisplayControl.DataGridView.SelectedRows.Count}】个进程，请谨慎操作！！！",UIStyle.Orange,true))
                {
                    return;
                }
            }
            else
            {
                this.ShowWarningNotifier("请从序号列选择需要结束的进程");
                return;
            }

            if(this.dataDisplayControl.DataGridView.SelectedRows.Count > 0)
            {
                if(!this.ShowAskDialog($"{GlobalData.AppName_zh}-敏感操作",$"再次确认是否结束进程！！！",UIStyle.Orange,true))
                {
                    return;
                }
            }

            foreach(DataGridViewRow row in this.dataDisplayControl.DataGridView.SelectedRows)
            {
                string sid = row.Cells["会话ID"].Value.ToString();
                string serial = row.Cells["串口号"].Value.ToString();
                string killSql = $"alter system kill session '{sid},{serial}'";
                try
                {
                    this.dbManagerBll.ExecuteOperationSql(killSql);

                    backgroundWorker.RunWorkerAsync(new Tuple<DbManagerBll,bool,int>(new DbManagerBll(this.dbInfo),false,0));

                    this.ShowSuccessNotifier($"会话{sid}已结束");

                    LogHelper.WriteInfoLog($"执行数据库解锁：{killSql}");

                    if(Setting.System.SendMsgModule.Contains("DbLockWatch"))
                    {
                        string dbip = !string.IsNullOrEmpty(this.dbInfo.Ip) ? this.dbInfo.Ip : new DbInfoBll().GetDbInfoByConnectionString(this.dbInfo.ConnectionString).Ip;

                        string msg = $"数据库：{this.dbInfo.NodeName}\\n数据库地址：{dbip}\\n服务器：{row.Cells["服务器"]}\\n实例：{row.Cells["实例"]}\\n锁类型：{row.Cells["锁类型"]}\\n锁对象：{row.Cells["锁对象"]}\\n串口号：{serial}\\n会话ID：{sid}\\n锁时长：{row.Cells["锁时长"]}\\n客户端IP：{row.Cells["客户端IP"]}\\n客户端名称：{row.Cells["客户端名称"].ToString().Replace(@"\",@"\\")}\\n程序名称：{row.Cells["程序名称"].ToString().Replace(@"\",@"\\")}\\n执行的解锁语句：{killSql}";

                        FeiShuMsgHelper.SendCardMsgAsync(msg,$"数据库锁表会话[{sid}]解锁",LogType.Warning);
                    }

                }
                catch(Exception ex)
                {
                    this.ShowErrorNotifier($"【{sid}】-【{serial}】{ex.Message}{DateTime.Now.Millisecond}");

                    if(Setting.System.SendMsgModule.Contains("DbLockWatch"))
                        FeiShuMsgHelper.SendCardMsgAsync(killSql,$"数据库锁表会话[{sid}]解锁失败",LogType.Error);

                    LogHelper.WriteErrLog(ex);
                }

            }

        }

        private void DbLockWatchForm_Disposed(object sender,EventArgs e)
        {
            try
            {
                this.backgroundWorker.CancelAsync();
                this.backgroundWorker.Dispose();
            }
            catch(Exception ex)
            {
                LogHelper.WriteErrLog(ex);
            }
        }
    }
}
